﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.Resources;
using PMPUtils.DB;
using PMPUtils.Util;
using PMPUtils.Configuration;
using CrystalDecisions.Web;

namespace PMPUtils.Report
{
    public class TMDashboardOperator
    {

        public static void RefreshOverallTMDashboard(CrystalReportSource CrystalReportSource1)
        {
            DataTable dtDashboard = RefreshTMDashboard("7/1/2011", "7/1/2012");
            CrystalReportSource1.ReportDocument.SetDataSource(dtDashboard);

            CrystalReportSource1.ReportDocument.SetDatabaseLogon(DBConfigurations.JIRA_RPT_SA, DBConfigurations.JIRA_RPT_PASSWORD);
            CrystalReportSource1.ReportDocument.Refresh();
        }

        public static void RefreshWeeklyTMDashboard(CrystalReportSource CrystalReportSource2)
        {
            String startDate = PMPUtil.GetLastSunday();
            String endDate = DateTime.Now.ToString("MM/dd/yyyy");

            DataTable dtDashboard = RefreshTMDashboard(startDate, endDate);
            CrystalReportSource2.ReportDocument.SetDataSource(dtDashboard);

            CrystalReportSource2.ReportDocument.SetDatabaseLogon(DBConfigurations.JIRA_RPT_SA, DBConfigurations.JIRA_RPT_PASSWORD);
            CrystalReportSource2.ReportDocument.Refresh();
        }

        public static void RefreshSprintTMDashboard(CrystalReportSource CrystalReportSource3)
        {
            String sprint = SprintOperator.ReadCurrentSprint();
            String startDate = SprintOperator.ReadSprintStart(sprint).ToString("MM/dd/yyyy");
            String endDate = DateTime.Now.ToString("MM/dd/yyyy");

            DataTable dtDashboard = RefreshTMDashboard(startDate, endDate);
            CrystalReportSource3.ReportDocument.SetDataSource(dtDashboard);

            CrystalReportSource3.ReportDocument.SetDatabaseLogon(DBConfigurations.JIRA_RPT_SA, DBConfigurations.JIRA_RPT_PASSWORD);
            CrystalReportSource3.ReportDocument.Refresh();
        }

        public static void RefreshOverallTMDashboardByTeam(CrystalReportSource CrystalReportSource4)
        {
            String startDate = "7/1/2011";
            String endDate = "7/1/2012";

            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            String VincentResourcesList = ResourceOperator.ReadVincentResources();
            String KimResourcesList = ResourceOperator.ReadKimResources();

            String strQueryDashboard = " select case when resource in (" + VincentResourcesList + ") then 'NF1'" +
                "            when resource in (" + KimResourcesList + ") then 'NF2'" +
                "            else 'Unknow' end 'Team'," +
                "       sum(estimate) Orig," +
                "       sum(actual) Actual," +
                "       cast((sum(actual) - sum(estimate)) / sum(estimate) as decimal(18, 4)) 'Variance'," +
                "       sum(Reworktimes) 'Reworktimes'," +
                "       count(pkey) 'NumberofJIRA'," +
                "       cast(cast(sum(Reworktimes) as decimal(18, 2))/count(pkey) as decimal(18, 2)) 'Quality'," +
                "       sum(case when IsOverdue = 1 then 1 else 0 end) 'NumberofOverdues'," +
                "       cast((count(pkey) - sum(case when IsOverdue = 1 then 1 else 0 end)) / cast(count(pkey) as decimal(18, 2)) as decimal(18, 4)) 'OnTime'" +
                " from" +
                " ( select  ji.assignee 'Resource', " +
                "       ji.pkey, " +
                "       coalesce(cfv_1.stringvalue, 'Unknown') Source," +
                "       it.pname issuetypename,ji.issuetype ,    " +
                "       case when duedate < dateadd(day,-1,ji.resolutiondate) then 1 else 0 end isoverdue," +
                "       coalesce(reworktimes,0) reworktimes," +
                "       coalesce(ji.timeoriginalestimate/3600,coalesce(ji.timespent/3600,0,ji.timespent/3600)) estimate," +
                "       coalesce(timespent/3600,0) actual" +
                " from lion.jira_reporting.dbo.jiraissue ji with (nolock)" +
                " join lion.jira_reporting.dbo.issuetype it with (nolock)" +
                "   on it.id = ji.issuetype" +
                " left join lion.jira_reporting.dbo.customfieldvalue cfv_1 with (nolock)" +
                "   /* source of jira */" +
                "   on cfv_1.customfield = '10041'   " +
                "  and cfv_1.issue = ji.id" +
                " left join (" +
                "         select count(1) reworktimes,issueid" +
                "           from lion.jira_reporting.dbo.changegroup cg with (nolock)" +
                "           join lion.jira_reporting.dbo.changeitem ci with (nolock)" +
                "             on ci.groupid = cg.id" +
                "          where ci.field='status' and newstring like 'reopened' and oldstring not like 'reopened'" +
                "          group by cg.issueid" +
                "    ) rework" +
                "    on rework.issueid = ji.id" +
                " where 1=1 and it.pname in ('Analysis','Integration Testing','Documentation','Approach','Sub-task','Code')" +
                "    /*time range*/" +
                "    and ji.resolutiondate>='" + startDate + "' " +
                "    and ji.resolutiondate<='" + endDate + "'  " +
                "    /* assignees */" +
                " and ji.assignee in (" + resourceList + ") ) results" +
                "  where case when resource in (" + VincentResourcesList + ") then 'NF1'" +
                "            when resource in (" + KimResourcesList + ") then 'NF2'" +
                "            else 'Unknow' end in ('NF1', 'NF2')" +
                "  group by case when resource in (" + VincentResourcesList + ") then 'NF1'" +
                "            when resource in (" + KimResourcesList + ") then 'NF2'" +
                "            else 'Unknow' end" +
                " union" +
                " select case when resource in (" + resourceList + ") then 'OPTM'" +
                "            else 'Unknow' end 'Team'" +
                "       ," +
                "       sum(estimate) Orig," +
                "       sum(actual) Actual," +
                "       cast((sum(actual) - sum(estimate)) / sum(estimate) as decimal(18, 4)) 'Variance'," +
                "       sum(Reworktimes) 'Reworktimes'," +
                "       count(pkey) 'NumberofJIRA'," +
                "       cast(cast(sum(Reworktimes) as decimal(18, 2))/count(pkey) as decimal(18, 2)) 'Quality'," +
                "       sum(case when IsOverdue = 1 then 1 else 0 end) 'NumberofOverdues'," +
                "       cast((count(pkey) - sum(case when IsOverdue = 1 then 1 else 0 end)) / cast(count(pkey) as decimal(18, 2)) as decimal(18, 4)) 'OnTime'" +
                " from" +
                " ( select  ji.assignee 'Resource', " +
                "       ji.pkey, " +
                "       coalesce(cfv_1.stringvalue, 'Unknown') Source," +
                "       it.pname issuetypename,ji.issuetype ,    " +
                "       case when duedate < dateadd(day,-1,ji.resolutiondate) then 1 else 0 end isoverdue," +
                "       coalesce(reworktimes,0) reworktimes," +
                "       coalesce(ji.timeoriginalestimate/3600,coalesce(ji.timespent/3600,0,ji.timespent/3600)) estimate," +
                "       coalesce(timespent/3600,0) actual" +
                " from lion.jira_reporting.dbo.jiraissue ji with (nolock)" +
                " join lion.jira_reporting.dbo.issuetype it with (nolock)" +
                "   on it.id = ji.issuetype" +
                " left join lion.jira_reporting.dbo.customfieldvalue cfv_1 with (nolock)" +
                "   /* source of jira */" +
                "   on cfv_1.customfield = '10041'   " +
                "  and cfv_1.issue = ji.id" +
                " left join (" +
                "         select count(1) reworktimes,issueid" +
                "           from lion.jira_reporting.dbo.changegroup cg with (nolock)" +
                "           join lion.jira_reporting.dbo.changeitem ci with (nolock)" +
                "             on ci.groupid = cg.id" +
                "          where ci.field='status' and newstring like 'reopened' and oldstring not like 'reopened'" +
                "          group by cg.issueid" +
                "    ) rework" +
                "    on rework.issueid = ji.id" +
                " where 1=1 and it.pname in ('Analysis','Integration Testing','Documentation','Approach','Sub-task','Code')" +
                "    /*time range*/" +
                "    and ji.resolutiondate>='" + startDate + "' " +
                "    and ji.resolutiondate<='" + endDate + "'  " +
                "    /* assignees */" +
                " and ji.assignee in (" + resourceList + ") ) results" +
                "  group by case when resource in (" + resourceList + ") then 'OPTM'" +
                "            else 'Unknow' end";

            DBOperator rptOperator = new DBOperator(DBConfigurations.JIRA_RPT_SQL_DB_CONNECTION);
            DataTable dtDashboard = rptOperator.Read(strQueryDashboard);
            CrystalReportSource4.ReportDocument.SetDataSource(dtDashboard);

            CrystalReportSource4.ReportDocument.SetDatabaseLogon(DBConfigurations.JIRA_RPT_SA, DBConfigurations.JIRA_RPT_PASSWORD);
            CrystalReportSource4.ReportDocument.Refresh();
        }

        private static DataTable RefreshTMDashboard(String startDate, String endDate)
        {
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");

            String strQueryDashboard = " select resource," +
                "        sum(estimate) Orig," +
                "        sum(actual) Actual," +
                "        cast((sum(actual) - sum(estimate)) / sum(estimate) as decimal(18, 4)) 'Variance'," +
                "        sum(Reworktimes) 'Reworktimes'," +
                "        count(pkey) 'NumberofJIRA'," +
                "        cast(cast(sum(Reworktimes) as decimal(18, 2))/count(pkey) as decimal(18, 2)) 'Quality'," +
                "        sum(case when IsOverdue = 1 then 1 else 0 end) 'NumberofOverdues'," +
                "        cast((count(pkey) - sum(case when IsOverdue = 1 then 1 else 0 end)) / cast(count(pkey) as decimal(18, 2)) as decimal(18, 4)) 'OnTime'" +
                " from" +
                " ( select  ji.assignee 'Resource', " +
                "       ji.pkey, " +
                "       coalesce(cfv_1.stringvalue, 'Unknown') Source," +
                "       it.pname issuetypename,ji.issuetype ,    " +
                "       case when duedate < dateadd(day,-1,ji.resolutiondate) then 1 else 0 end isoverdue," +
                "       coalesce(reworktimes,0) reworktimes," +
                "       coalesce(ji.timeoriginalestimate/3600,coalesce(ji.timespent/3600,0,ji.timespent/3600)) estimate," +
                "       coalesce(timespent/3600,0) actual" +
                " from lion.jira_reporting.dbo.jiraissue ji with (nolock)" +
                " join lion.jira_reporting.dbo.issuetype it with (nolock)" +
                "   on it.id = ji.issuetype" +
                " left join lion.jira_reporting.dbo.customfieldvalue cfv_1 with (nolock)" +
                "   /* source of jira */" +
                "   on cfv_1.customfield = '10041'   " +
                "  and cfv_1.issue = ji.id" +
                " left join (" +
                "         select count(1) reworktimes,issueid" +
                "           from lion.jira_reporting.dbo.changegroup cg with (nolock)" +
                "           join lion.jira_reporting.dbo.changeitem ci with (nolock)" +
                "             on ci.groupid = cg.id" +
                "          where ci.field='status' and newstring like 'reopened' and oldstring not like 'reopened'" +
                "          group by cg.issueid" +
                "    ) rework" +
                "    on rework.issueid = ji.id" +
                " where 1=1 and it.pname in ('Analysis','Integration Testing','Documentation','Approach','Sub-task','Code')" +
                "    /*time range*/" +
                "    and ji.resolutiondate>='" + startDate + "' " +
                "    and ji.resolutiondate<='" + endDate + "'  " +
                "    /* assignees */" +
                " and ji.assignee in (" + resourceList + ") ) results" +
                "   group by resource";

            DBOperator rptOperator = new DBOperator(DBConfigurations.JIRA_RPT_SQL_DB_CONNECTION);
            DataTable dtDashboard = rptOperator.Read(strQueryDashboard);

            return dtDashboard;
        }
    }
}
